#!/usr/bin/perl
#
# content2db.pl version 1.1
#
# A quick* and dirty program to parse the dmoz/ODP content RDF file.
# Before running this script, the RDF file must be cleaned up by
# removing invalid UTF-8 sequences and illegal XML chars. You can zap
# the bad UTF-8 sequences like so:
#   iconv -c -f UTF-8 -t UTF-8 structure.rdf.u8 > valid.rdf.u8
# You can strip the bad XML with a simple regex like this:
#   s/[\x00-\x08\x0b-\x0c\x0e-\x1f]//g;
# 
# Here's some SQL to creat the table expected by this program:
#
# CREATE TABLE "xurls" (
#   catid INT,
#   priority INT,
#   ages VARCHAR(32),
#   mediadate VARCHAR(32),
#   url VARCHAR(1024),
#   title VARCHAR(1024),
#   description VARCHAR(8192)
# );
#
# Notes:
# 1. The category records are ignored as this information is duplicated in 
# the structure.rdf.u8 file. Only the external page records are loaded.
#
# * "quick" does not refer to run time. Expect a long wait for a full
# content file to be loaded into the database!
#
# History
# 
# 2004 Feb 20 - v1.1 Added patches from will@hellacool.co.uk that simplify
#                    the DBI code by using placeholders.
#
#==========================================================================
# Copyright (c) 2003-2004 by R. Steven Rainwater <steve@ncc.com>
# 
# http://www.ncc.com/freeware/
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
#==========================================================================
#
use strict;
use bytes;
use XML::Parser;
use DBI;

$| =1;

# Globals containing the current category information
my $catid = '';
my $ages = '';
my $priority = '';
my $mediadate = '';
my $url = '';
my $title = '';
my $description = '';

my $records = 0;

# Sort of an XML tag stack thingy
my @pendingTag = ();

# connect to database
my $dbh = DBI->connect("dbi:Pg:dbname=name;host=db.host.com","username","password")
or die "Can't connect to database: $DBI::errstr\n";

# purge any existing content data
$dbh->do("DELETE FROM xurls") or print "\nDelete Failed: $DBI::errstr\n";

# prepare a db handle with the insert statement with placeholders
my $sth = $dbh->prepare("INSERT INTO xurls VALUES (?, ?, ?, ?, ?, ?, ?)");

# initialize XML parser
my $p1 = new XML::Parser(Style => 'Stream',
                      ErrorContext => 2,
		      ProtocolEncoding => 'UTF-8',
		      NoExpand => 1,
		      ParseParamEnt => 0);

# parse the file
$p1->parsefile('content.clean');
$dbh->disconnect or die "Disconnect failed: $DBI::errstr\n";
exit;

# XML processing subs

sub StartDocument {
  print "\nLoading content records\n";
  return;
}

sub StartTag {
  m/^<(\S+)[\s>].*/;
  my $tag = $1;
  unshift @pendingTag, $tag;
  if($tag eq 'Topic') { $catid = ''; }
  elsif($tag eq 'ExternalPage') {
    m/.*"(.+)">$/;
    $url = $1;
    $title = '';
    $description = '';
    $ages = '';
    $priority = '';
    $mediadate = '';
  }
  elsif($tag eq 'RDF' || $pendingTag[1] eq 'Topic' || $pendingTag[1] eq 'ExternalPage') { return; }
  else { print "Warning: Unhandled Tag [$tag] (Pending [$pendingTag[1]])\n"; }
}

sub EndTag {
  m/^<\/(\S+)>$/;
  my $tag = $1;
  shift @pendingTag;
  if($tag eq 'ExternalPage') {
    # insert this topic block into database
    $sth->execute($catid, $priority || 0, $ages || '', $mediadate || '', $url, $title, $description)
    or print 
      "\n------\n",
      "Structure Insert Failed: $DBI::errstr\n",
      "Catid: $catid\n",
      "Title: $title\n",
      "URL: $url\n",
      "Desc: $description\n",
      "Ages: $ages\n",
      "Priority: $priority\n",
      "Media Date: $mediadate\n";
      
    $records++;
    unless($records % 1000) { print "Record: $records\n"; }
  }
}

sub Text {
  if($pendingTag[0] eq 'd:Title') { $title = $_; }
  elsif($pendingTag[0] eq 'd:Description') { $description = $_; }
  elsif($pendingTag[0] eq 'catid') { $catid = $_; }
  elsif($pendingTag[0] eq 'priority') { $priority = $_; }
  elsif($pendingTag[0] eq 'mediadate') { $mediadate = $_; }
  elsif($pendingTag[0] eq 'ages') { $ages = $_; }
}

sub PI {
  return;
}

sub EndDocument {
  print "$records loaded\n";
  return;
}
